using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Windows.Forms;
using System.Data;

namespace AdatbazisProjekt
{
    public abstract class DalGen // az osztaly az par adatbazis kezelo metodust gyujt ossze ami elosegitheti a tovabbi munkamat
    {
        protected static bool ConnectionCreated;
        protected static bool kapcsolodva;
        protected static SqlConnection mySqlConn; 
        protected string strSqlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Programs\\SQL\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\azEnyem.mdf";
        protected SqlDataReader myDataReader;
        protected SqlCommand myComm; 


        protected bool IsConnectCreated()
        {
            return ConnectionCreated;
        }

        protected bool IsConnected()
        {
            return kapcsolodva;
        }

        protected bool MakeConnection()
        {
            // Create the Connection if is was not already created.
            if (ConnectionCreated != true)
            {
                try
                {
					mySqlConn = new SqlConnection("user id=n0r_ka;" +
									   "server=(local);" +
									   "Trusted_Connection=yes;" +
									   "database=azEnyem; " +
									   "connection timeout=10");
                    mySqlConn.Open();
                    ConnectionCreated = true;
                    mySqlConn.Close();
                    kapcsolodva = false;
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else { return true; }
        }

        protected bool OpenConnection()
        {
            // Open the Connection when the state is not already open.
            if (kapcsolodva != true)
            {
                try
                {
                    mySqlConn.Open();
                    kapcsolodva = true;
                    return true;
                }
                catch
                {
                    return false;
                }

            }
            else { return true; }
        }

        internal void CloseConnection()
        {
            // Close the Connection when the connection is opened.
            if (kapcsolodva == true)
            {
                mySqlConn.Close();
                kapcsolodva = false;
            }
        }

        protected SqlDataReader mExecuteReader(string sQuery, ref string ErrMess,SqlTransaction tran)
        {
			// a select paracs eredmenyet kuldi vissza egy SqlDataReader obijektumban
			SqlDataReader mDataReader;
            try
            {
                this.OpenConnection();
       
                myComm = new SqlCommand(sQuery, mySqlConn);
                
                if (tran != null)
                {
                    myComm.Transaction = tran;
                }

                mDataReader = myComm.ExecuteReader();
                ErrMess = "OK";
                //this.CloseConnection();
                myComm.Dispose();
                return mDataReader;

            }
            catch (Exception e)
            {
                // ROLLBACK ###############
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                //######################
                ErrMess = e.Message;
                this.CloseConnection();
                MessageBox.Show(ErrMess);
                return null;
            }
        }

		protected  SqlConnection GetConnection(){
			return  mySqlConn;
		}

        protected void MyExecuteNonQuery(string commandText, ref bool error,SqlTransaction tran)
        {
			// az olyan sql parancsok futattasara alkalmaz fuggveny melyeknek nincs vissza teresi erteke
			// lasd INSERT DELETE EXEC
            try
            {
                this.OpenConnection();
                // TRANZACIO ####################
                
                myComm = new SqlCommand(commandText, mySqlConn);
                if (tran != null)
                {
                    myComm.Transaction = tran;
                }
                //####################
                myComm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // ROLLBACK ###############
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                //######################
                MessageBox.Show("Hiba a: '" + commandText + "' parancs vegrehajtasa kozben: "+e.Message);
                this.CloseConnection();
                error = true;
            }
         
        }

        protected void MyExecuteNonQueryWithParameters(String cmd,List<String> parameterNames, List<String> parameterValue, SqlTransaction tran)
        {
			// Fokent parameterrekkel elallatott tartol eljarasok futattasara alkalmas a metodus
            try
            {
                this.OpenConnection();
                // TRANZACIO ####################

                    
                myComm = new SqlCommand(cmd, mySqlConn);
                if (tran != null)
                {
                    myComm.Transaction = tran;
                }
                //####################
                List<SqlParameter> param = new List<SqlParameter>();
                SqlParameter p;
                for (int i = 0; i < parameterNames.Count; i++)
                {
                    p = new SqlParameter();
                    p.ParameterName = parameterNames[i];
                    p.Value = parameterValue[i];
                    param.Add(p);
                }
                for (int i = 0; i < param.Count; i++ )
                {
                    myComm.Parameters.Add(param[i]);
                }
                myComm.ExecuteNonQuery();
                myComm.Dispose();
                this.CloseConnection();
            }
            catch (Exception e)
            {
                MessageBox.Show("Hiba a parameteres eljaras vegrehajtasa kozben!" + e.Message);
                // ROLLBACK ###############
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                //######################
                this.CloseConnection();
            }
        }

        public DataTable QueryCmd(String sqlCmd)
        {
            try
            {
                this.OpenConnection();
                SqlCommand MyCmd = new SqlCommand(sqlCmd, mySqlConn);
                SqlDataReader dr = MyCmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception es) { throw (es); }
            finally { this.CloseConnection(); }
        }
 

    }
}
